# -*- coding: utf-8 -*-

from datetime import datetime

from sqlalchemy import *

from sqlalchemy.orm import *

import time, random, md5

import os
import sys

from time import time, localtime, strftime


#for $tab in $tables
"""
class: $tab.capitalize()
"""
class $tab.capitalize()(object):
    """
    
    """
    def __init__(self):
        pass

#end for

class DB:

    def __init__(self, echo = False):
        print "create engine"
        self.err_count = 0
        uri = 'sqlite:///data.dat'
        #uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/vpipe?charset=utf8&use_unicode=0'
        self.engine = create_engine(uri,encoding='utf-8')
        metadata = MetaData(self.engine)
        metadata.bind.echo  = echo

#for $tab in $tables
###$tab.lower()
#set $ltab = $tab.lower()
        
        """
        taqble: ${ltab}
        """
        ${ltab} = Table('${ltab}', metadata,
#set one = $tables[$tab]
#set colslen = len(one)
###$one
#for $row in range(1,$colslen+1)
#set $col = $tables[$tab][$row]
###$col.name
    #if $col.is_pk == 1
            Column('${col.name}', ${col.data_type}, primary_key=True, autoincrement=True, nullable=${col.nullable}), ###default=${col.default}
    #else
            Column('${col.name}', ${col.data_type}, nullable=${col.nullable} ${col.default_value}), ####${col.nullable}
    #end if
#end for
            Column('project', String(36), nullable=False, default='kylin')
        )
#end for
        metadata.create_all()
        
        """
        mapper
        """
#for $tab in $tables
        mapper($tab.capitalize(),$tab.lower())
#end for

    def execute(self, sql):
        sql = sql.decode('GBK')
        connection = self.engine.connect()
        try:
            result = connection.execute(sql)
            return result
        except Exception, e:
            self.err_count = self.err_count + 1
            print sql.encode('utf8')
            print e
            if self.err_count > 6:
                sys.exit(0)

def test():
  db = DB(True)

  file = open('metadata.sql','r')
  i = 0
  sqlp = []
  j = 0
  for line in file:
      i = i +1

      line = line.strip()
      now = strftime("%Y-%m-%d %H:%M:%S", localtime())
      now = "'%s'" % now

      line = line.replace('now()',now)
      if line[-2:] ==');':
          sqlp.append(line)
          mysql = ''
          for sql in sqlp:
              mysql += sql
          print mysql #.decode('utf8')
          j = j +1
          if j >3:
              pass
          db.execute(mysql)
          db.execute('PRAGMA table_info("models")')
          print "\n-----------\n"
          sqlp = []
          
          #print line, i
      else:
        sqlp.append(line)

if __name__ == "__main__":
    test()
